VBscript to read MSSQL Database table value in Siemens Wincc HMI

Published on Jan06, 2025 | Category: tia wincc
Share this article:

in this article we know how to connect MSSQL database table to read table value wincc hmi. to connect MSSQL database first connect database than read value of table. to do this first create a data base in same database create a table. second create a DSN to connect MSSQL database to VBSCRIPT. it is very simple to read and write value using vbscript in wincc HMI. VBScript have so many features. To read values from an MSSQL table using VBScript, you can use ADO (ActiveX Data Objects). in this article have simple VBscript help you connect to an MSSQL database, execute a query, and read table values.

what is Microsoft SQL server Management studio

Microsoft SQL Server Management Studio (SSMS) is an integrated environment for managing, configuring, and interacting with SQL Server instances. It is a powerful tool provided by Microsoft for database administrators (DBAs), developers, and analysts to manage databases, write queries, and perform administrative tasks. SSMS is free and can be downloaded from the official Microsoft website.

img/wincc vbscript for sql/what is Microsoft SQL server Management studio.webp

how to create tag database in MSSQL

first step to create tag database to store tag value so we can read and write these tag value. a tag database an organized collection of tag values that can be easily accessed, managed, and updated. You can create database by Using the GUI (Graphical User Interface) or Using SQL Query. follow these steps to create a database in MSSQL. Using the GUI (Graphical User Interface):

  1. In the Object Explorer, expand the Server node.
  2. Right-click on the Databases folder.
  3. Select New Database from the context menu.
  4. In the New Database window:
    • Enter the Database Name (tagdatabase).
    • Review or modify settings like file locations or sizes if needed (optional).
  5. Click OK to create the database.

img/wincc vbscript for sql/how to create tag database in MSSQL.webp

how to create database Using SQL Query:

  1. Open a new query window by clicking New Query in the toolbar.
  2. Write the following SQL code: "CREATE DATABASE tagdatabase2;"
  3. Execute the query by pressing F5 or clicking the Execute button.

img/wincc vbscript for sql/how to create database Using SQL Query.webp

how to create tag table in MSSQL

A table in a database is a structured format for storing data. It organizes data into rows and columns, making it easy to manage, retrieve, and manipulate. Tables are a fundamental component of relational databases. you can create table in mssql by using GUI or SQL query Method 1: Using the GUI (Graphical User Interface)

  1. Open the Table Designer:
    • Right-click on the Tables folder and select New Table.
    2. img/wincc vbscript to read mssql tag table/Open the Table Designer.webp
  2. Define Columns:
    • Enter the column names, data types (e.g., INT, NVARCHAR(50)), and constraints (e.g., NOT NULL, PRIMARY KEY).
  3. Set a Primary Key (Optional):
    • Right-click the row for the primary key column and select Set Primary Key.
    4. img/wincc vbscript to read mssql tag table/Define Columns.webp
  4. Save the Table:
    • Click File > Save tagtable (or press Ctrl+S) and provide a name for the table.

img/wincc vbscript to read mssql tag table/how to create tag table in MSSQL.webp

Method 2: create table Using SQL Queries

  1. Open a New Query Window:
    • In SSMS, click New Query from the toolbar.
  2. Write a SQL Script:
    • Example SQL to create a Users table:
      • 
USE tagdatabase2; -- Switch to the database

CREATE TABLE tagtable (
    id INT PRIMARY KEY,        -- Unique identifier
    tagname NVARCHAR(50) NOT NULL, -- Tag name
    tagvalue INT NOT NULL,         -- Tag value
    JoinDate DATE DEFAULT GETDATE() -- Default to current date
);
  3. Execute the Script:
    • Press F5 or click the Execute button to create the table.

img/wincc vbscript to read mssql tag table/create table Using SQL Queries.webp

add value in tag table

after creating table add following value to tagtable right click on tag table and then select edit top 200 rows img/wincc vbscript to read mssql tag table/right click on tag table and then select edit top 200 rows.webp add following value to table img/wincc vbscript to read mssql tag table/add following value to table.webp or execute following sql query directly to insert value in tagtable img/wincc vbscript to read mssql tag table/execute following sql query directly to insert value in tagtable.webp

how to configure dsn for wincc sql server

A Data Source Name (DSN) is a configuration that provides the necessary information for an application to connect to a database, such as the database driver, server address, and authentication details. DSN commonly stands for Data Source Name, a configuration used in computing to define how an application connects to a database. Step 1: Open ODBC Data Source Administrator

  1. open control panel
  2. open windows tools
  3. Choose the appropriate version of ODBC:
    • Use the 32-bit version if your application is 32-bit.
    • Use the 64-bit version if your application is 64-bit.
here we select 32 bit version for this article.

img/wincc vbscript for sql/how to configure dsn for sql server.webp

Step 2: Add a New DSN

  1. In the ODBC Data Source Administrator, go to the System DSN or User DSN tab:
    • System DSN: Available to all users on the system.
    • User DSN: Available only to the current user.
  2. Click the Add button.

img/wincc vbscript for sql/Step 2 Add a New DSN.webp

select driver for DSN

  • Select SQL Server or ODBC Driver for SQL Server from the list of drivers and click Finish.

    • img/wincc vbscript for sql/configure the dsn.webp
    Step 3: Configure the DSN
    1. In the Create a New Data Source to SQL Server window:
      • Enter a name for the DSN (e.g., MySql).
      • Provide an optional description.
      • Select the SQL Server instance to connect.
    2. Click Next.

    img/wincc vbscript for sql/enter name of DSN.webp

    Step 4: Configure Authentication

    1. Choose the authentication method:
      • Windows Authentication: Uses the current Windows user's credentials.
      • SQL Server Authentication: Requires a username and password.
    2. Click Next.

    img/wincc vbscript for sql/Configure Authentication.webp

    Step 5: Configure the Default Database

    1. Select the default database for the DSN from the dropdown menu.
    2. Click Next.

    img/wincc vbscript for sql/Configure the Default Database.webp

    Step 6: Test the DSN

    1. Click the Test Data Source button to ensure the DSN is working correctly.
    2. If the test is successful, click Finish.

    img/wincc vbscript for sql/Test the DSN.webp

    open tia portal and create a project

    open tia portal and create a new project enter details of project or add hmi in your project. in this example we have a button and two text field. when button is pressed value of sql table is show in text field. add a script and rename to read_sql table. Assign vbscript to button1 press event property. when button is pressed value of selected table column show in text field. if any error comes during connection of read value it shows on text field.

    img/wincc vbscript to read mssql tag table/VBscript to read MSSQL Database table value in Siemens Wincc HMI.webp

    VBscript to read MSSQL Database table value in Siemens Wincc HMI

    example of vbscript to read MSSQL Database table value in Siemens Wincc HMI This script demonstrates how to connect to a database using ODBC, execute an SQL query, and display the results in a human-machine interface (HMI).

    img/wincc vbscript to read mssql tag table/example of vbscript to read MSSQL Database table value in Siemens Wincc HMI.webp

    1. Several variables are defined to manage the database connection, SQL query, and text fields:
      • conn: The database connection object.
      • strConn: The connection string for the ODBC Data Source Name (DSN).
      • sqlSelect: The SQL query to retrieve the desired value.
      • dsn: The ODBC DSN name (e.g., "MySql").
      • valueRead: Holds the value retrieved from the database.
      • textfield1 and textfield2: References to text fields in the HMI runtime.
    2. Setting Up HMI Text Fields : References to the text fields are set using the HmiRuntime.Screens object: 
      Set textfield1 = HmiRuntime.Screens("Screen_1").ScreenItems("Text field_1")
Set textfield2 = HmiRuntime.Screens("Screen_1").ScreenItems("Text field_2")
      These text fields are used to display the results of the script.
    3. The ODBC DSN is specified, and the connection string is constructed: 
      dsn = "MySql"
strConn = "DSN=" & dsn & ";Trusted_Connection=Yes;"
      The Trusted_Connection=Yes setting enables Windows Authentication.
    4. The SQL query retrieves the value where tagname = 'temprature1': 
      sqlSelect = "SELECT tagvalue FROM tagtable WHERE tagname = 'temprature1'"
    5. Database Connection

      The script creates and opens the connection using the ADO object:

      Set conn = CreateObject("ADODB.Connection")
conn.Open strConn
      Error handling is enabled using On Error Resume Next.
    6. Executing the Query The query is executed, and the result is checked: 
      Set rs = conn.Execute(sqlSelect)
If Not rs.EOF Then
    valueRead = rs.Fields("tagvalue").Value
    textfield1.Text = "Value retrieved: " & valueRead
Else
    textfield2.Text = "No record found for tagname 'temprature1'."
End If
      If the query returns a result, the value is displayed in textfield1. Otherwise, an error message is shown in textfield2.
    7. Error Handling and Cleanup If the connection fails, an error message is displayed: 
      textfield2.Text = "Failed to connect to the database."
      All resources (recordset and connection) are closed and cleaned up at the end: 
      If conn.State = 1 Then conn.Close
Set conn = Nothing
    Important Notes

    Recommended for You